﻿using RndMaterialDisposalService.Attrbutes;
using RndMaterialDisposalService.Repository;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System.Data;
using System.Reflection;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;

namespace RndMaterialDisposalService.Extensions
{
    public class EFExtension
    {
        public List<T> SqlQuery<T>(DbContext dbContext, string sql, List<SqlParameter> sqlParameters) where T : class, new()
        {
            List<T> list = new List<T>();
            var conn = dbContext.Database.GetDbConnection();
            var comm = conn.CreateCommand();
            try
            {
                conn.Open();
                comm.CommandText = sql;
                if (sqlParameters.Count > 0)
                {
                    sqlParameters.ForEach((x) => {
                        comm.Parameters.Add(x);
                    });
                }

                var dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    T t = new T();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var columnName = dr.GetName(i);
                        var colDataType = dr.GetFieldType(i);
                        var item = dr.GetFieldValue<object>(i);
                        if (item == DBNull.Value)
                        {
                            item = null;
                        }
                        //var fieid = t.GetType().GetField(columnName);
                        //fieid?.SetValue(t, item);
                        var property = t.GetType().GetProperty(columnName);
                        property?.SetValue(t, item);
                    }
                    list.Add(t);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return list;
        }
        public DataSet SqlQueryDataSet<T>(DbContext dbContext, string sql) where T : class, new()
        {
            var conn = dbContext.Database.GetDbConnection() as SqlConnection;
            DataSet dataSet = new DataSet();
            try
            {
                using (SqlCommand command = new SqlCommand(sql, conn))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(dataSet);
                    return dataSet;
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                if (conn != null && conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}
